<?php 
/* 	These code was generated using phpCIGen v 2.a (21/05/2012)
	#zaqi 		zaqi.smart@gmail.com,http://zenzaqi.blogspot.com,
    #CV. Trust Solution, jl. Saronojiwo 2/19 Surabaya, http://www.ts.co.id

	+ Module  		: t_add Model
	+ Description	: For record model process back-end
	+ Filename 		: c_t_add.php
 	+ creator 		: 
 	+ Created on 24/Jul/2012 10:52:52

*/

class M_t_add extends CI_Model{

	//constructor
	function __construct() {
		parent::__construct();
	}
	
	function adddet_list($kontrak_no,$add_id) {
		$query = "SELECT  m_div_id FROM t_kontrak
			LEFT JOIN t_spk ON t_spk.spk_id = t_kontrak.t_spk_id
			LEFT JOIN t_komp ON t_spk.t_komp_id = t_komp.komp_id
			LEFT JOIN m_job 
			ON t_komp.m_job_id = m_job.job_id WHERE kontrak_no = '".$kontrak_no."'";
		$row = $this->db->query($query);
		$divisi = $row->row('m_div_id');
	
		if ( $divisi == 1){
			$sql = "
				SELECT 
					IFNULL(KOMP.spkdet_id,AD.spkdet_id) as spkdet_id,
					IFNULL(AD.adddet_id,KOMP.adddet_id) AS adddet_id,
					IFNULL(KOMP.jobdet_id,AD.jobdet_id) as jobdet_id,
					IFNULL(KOMP.jobdet_name,AD.jobdet_name) as jobdet_name,
					KOMP.volume AS volume,
					AD.volume_add AS volume_add,
					IFNULL(AD.unit_id,KOMP.unit_id) AS unit_id,
					IFNULL(AD.unit_name,KOMP.unit_name) AS unit_name,
					IFNULL(KOMP.kompdet_price,AD.kompdet_price) as kompdet_price,
					IFNULL(AD.adddet_price,KOMP.adddet_price) AS adddet_price,
					IFNULL(AD.status,KOMP.status) AS status 
				FROM (
				SELECT 0 As spkdet_id, adddet_id, jobdet_id, jobdet_name, 0 as volume,adddet_vol AS volume_add, adddet_unit AS unit_id, unit_name, 0 AS kompdet_price, adddet_price, 'addendum' AS status
				FROM t_add JOIN t_adddet ON(t_adddet.t_add_id = t_add.add_id)
				JOIN t_kontrak ON(t_kontrak.kontrak_id = t_add.t_kontrak_id)
				LEFT JOIN m_jobdet ON(m_jobdet.jobdet_id = t_adddet.m_jobdet_id)
				LEFT JOIN m_unit ON(m_unit.unit_id = t_adddet.adddet_unit)
				WHERE t_kontrak.kontrak_no='".$kontrak_no."'
				AND t_adddet.t_add_id = '". $add_id ."'
				) AS AD
				left JOIN
				(
				SELECT spkdet_id, 0 AS adddet_id, jobdet_id, jobdet_name, kompdet_vol AS volume, 0 as volume_add, unit_id, unit_name, kompdet_price, 0 AS adddet_price, '-' AS status
				FROM t_spkdet JOIN t_kontrak ON(t_kontrak.t_spk_id = t_spkdet.t_spk_id)
				JOIN t_kompdet ON(t_kompdet.kompdet_id = t_spkdet.t_kompdet_id)
				LEFT JOIN m_jobdet ON(m_jobdet.jobdet_id = t_kompdet.m_jobdet_id)
				LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
				WHERE t_kontrak.kontrak_no='".$kontrak_no."'
				) AS KOMP
				ON KOMP.jobdet_id = AD.jobdet_id
				"; 
				if($add_id == 0){
					$sql .= " UNION SELECT spkdet_id, 0 AS adddet_id, jobdet_id, jobdet_name, kompdet_vol AS volume, 0 as volume_add, unit_id, unit_name, kompdet_price, 0 AS adddet_price, '-' AS status
					FROM t_spkdet JOIN t_kontrak ON(t_kontrak.t_spk_id = t_spkdet.t_spk_id)
					JOIN t_kompdet ON(t_kompdet.kompdet_id = t_spkdet.t_kompdet_id)
					LEFT JOIN m_jobdet ON(m_jobdet.jobdet_id = t_kompdet.m_jobdet_id)
					LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
					WHERE t_kontrak.kontrak_no='".$kontrak_no."'";
				}
		}else{
			$sql = "
				SELECT * FROM (
			
				SELECT spkdet_spec, node.kompdet_lft, node.kompdet_id AS kompdet_id, jobdet_id, 
				IFNULL( CONCAT( REPEAT ( '--', COUNT(parent.kompdet_id)- 1 ), jobdet_name ), node.kompdet_uraian )AS jobdet_name, 
				(COUNT(parent.kompdet_id) - 1)AS depth, spkdet_id, adddet_id, 
				IFNULL(a.adddet_vol,node.kompdet_vol) AS volume_a,
				a.adddet_vol as volume_add, node.kompdet_vol as volume,
				unit_id, unit_name, node.kompdet_price, adddet_price, 
				IF(a.adddet_vol is null,'-','addendum') AS status  FROM ( 
				SELECT t_kompdet.* FROM t_spkdet JOIN t_kontrak ON( t_kontrak.t_spk_id = t_spkdet.t_spk_id ) 
				JOIN t_kompdet ON( t_kompdet.kompdet_id = t_spkdet.t_kompdet_id ) WHERE t_kontrak.kontrak_no = '".$kontrak_no."' )as node 
				Join( SELECT t_kompdet.* FROM t_spkdet JOIN t_kontrak ON( t_kontrak.t_spk_id = t_spkdet.t_spk_id ) 
				JOIN t_kompdet ON( t_kompdet.kompdet_id = t_spkdet.t_kompdet_id ) WHERE t_kontrak.kontrak_no ='".$kontrak_no."' )as parent 
				ON( node.kompdet_lft BETWEEN parent.kompdet_lft AND parent.kompdet_rgt ) left join m_jobdet 
				ON m_jobdet.jobdet_id = node.m_jobdet_id LEFT JOIN t_spkdet ON( t_spkdet.t_kompdet_id = node.kompdet_id ) 
				LEFT JOIN (select * from t_adddet WHERE t_adddet.t_add_id ='". $add_id ."') as a ON( a.m_jobdet_id = node.m_jobdet_id ) 
				LEFT JOIN m_unit ON( m_unit.unit_id = node.kompdet_unit ) 
				GROUP BY node.kompdet_id ORDER BY node.kompdet_lft 
				
				) t
				UNION ALL 
				SELECT '' as spkdet_spec, 0 as kompdet_lft, 0 as kompdet_id, jobdet_id, 
				jobdet_name, 0 as depth, 0 as spkdet_id, adddet_id, adddet_vol AS volume,adddet_vol AS addvol, 0 as volkom,
				unit_id, unit_name, 0 as kompdet_price, adddet_price, 'addendum' AS status
				from t_adddet
				LEFT JOIN t_kompdet
				ON t_adddet.m_jobdet_id = t_kompdet.m_jobdet_id
				left join m_jobdet 
				ON m_jobdet.jobdet_id = t_adddet.m_jobdet_id 
				LEFT JOIN m_unit ON( m_unit.unit_id = t_adddet.adddet_unit ) 
				where kompdet_id is null AND t_adddet.t_add_id ='". $add_id ."'";
			}
		$result = $this->db->query($sql);
		$nbrows = $result->num_rows();
	
		if($nbrows>0){
			foreach($result->result() as $row){
				$arr[] = $row;
			}
			$jsonresult = json_encode($arr);
			return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
		} else {
			return '({"total":"0", "results":""})';
		}
	}
	
	function get_jobdet_all($filter, $start, $end){
		$query = "SELECT IFNULL(spkdet_id, 0) AS spkdet_id, jobdet_id, jobdet_name
				FROM m_jobdet LEFT JOIN t_kompdet ON(t_kompdet.m_jobdet_id = m_jobdet.jobdet_id)
				LEFT JOIN t_spkdet ON(t_spkdet.t_kompdet_id = t_kompdet.kompdet_id)";
	
		// For simple search
		if ($filter<>""){
			$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
			$query .= " (jobdet_name LIKE '%".addslashes($filter)."%' )";
		}
	
		$result = $this->db->query($query);
		$nbrows = $result->num_rows();
		// $limit = $query." LIMIT ".$start.", ".$end;
		// $result = $this->db->query($limit);
	
		if($nbrows>0){
			foreach($result->result() as $row){
				$arr[] = $row;
			}
			$jsonresult = json_encode($arr);
			return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
		} else {
			return '({"total":"0", "results":""})';
		}
	}

	//function for get list record
	function t_add_list($filter,$start,$end){
		//menampilkan kontrak yang telah diterbitkan, sehingga "simple search" bisa berdasarkan hanya No.Kontrak
		$sql = "SELECT add_id, add_type, add_seq, add_reqdate, add_reqno, add_accdate, add_accno, add_date, add_no, add_begin, add_end, add_value,
					branch_id, branch_name, project_id, project_code, project_name, job_id, job_name, vendor_id, vendor_name, 
					kontrak_id, kontrak_no, kontrak_begin, kontrak_end
				FROM t_add JOIN t_kontrak ON(t_kontrak.kontrak_id = t_add.t_kontrak_id)
				JOIN t_spk ON(t_spk.spk_id = t_kontrak.t_spk_id)
				LEFT JOIN t_komp ON(t_komp.komp_id = t_spk.t_komp_id)
				LEFT JOIN m_branch ON(m_branch.branch_id = t_komp.m_branch_id)
				LEFT JOIN t_project ON(t_project.project_id = t_komp.t_project_id)
				LEFT JOIN m_job ON(m_job.job_id = t_komp.m_job_id)
				LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)";
		
		// For simple search
		if ($filter<>""){
			$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
			$sql .= " (kontrak_no LIKE '%".addslashes($filter)."%' OR vendor_name LIKE '%".addslashes($filter)."%' OR project_code LIKE '%".addslashes($filter)."%'
				OR project_name LIKE '%".addslashes($filter)."%' OR job_name LIKE '%".addslashes($filter)."%')";
		}

		$result = $this->db->query($sql);
		$nbrows = $result->num_rows();
		$limit = $sql." LIMIT ".$start.", ".$end;
		$result = $this->db->query($limit);

		if($nbrows>0){
			foreach($result->result() as $row){
				$arr[] = $row;
			}
			$jsonresult = json_encode($arr);
			return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
		} else {
			return '({"total":"0", "results":""})';
		}
	}

	//function for create new record
	function t_add_create($add_kontrak, $add_type, $add_seq, $add_req_date, $add_req_no, $add_acc_date, $add_acc_no, $add_date, $add_no, 
				$add_begin_date, $add_end_date, $add_value,
				$array_adddet_id, $array_badet_id, $array_jobdet_id, $array_volume, $array_unit_id, $array_adddet_price){
		$data = array(
				"t_kontrak_id"=>$add_kontrak,
				"add_type"=>$add_type,
				"add_seq"=>$add_seq,
				"add_reqdate"=>$add_req_date,
				"add_reqno"=>$add_req_no,
				"add_accdate"=>$add_acc_date,
				"add_accno"=>$add_acc_no,
				"add_date"=>$add_date,
				"add_no"=>$add_no,
				"add_begin"=>$add_begin_date,
				"add_end"=>$add_end_date,
				"add_value"=>$add_value
				);
		$this->db->insert('t_add', $data);
		if($this->db->affected_rows()){
			$add_id = $this->db->insert_id();
			for($i = 0; $i < sizeof($array_jobdet_id); $i++){
				$adddet_id = $array_adddet_id[$i];
				$badet_id = $array_badet_id[$i];
				$jobdet_id = $array_jobdet_id[$i];
				$volume = $array_volume[$i];
				$unit_id = $array_unit_id[$i];
				$adddet_price = $array_adddet_price[$i];
				
				$data_detail = array(
						"t_add_id"=>$add_id,
						"m_jobdet_id"=>$jobdet_id,
						"adddet_vol"=>$volume,
						"adddet_unit"=>$unit_id,
						"adddet_price"=>$adddet_price
						);
				if(($adddet_id == 0) && ($badet_id == 0)){
					//insert to detail
					$this->db->insert('t_adddet', $data_detail);
				}elseif (($adddet_id > 0) && ($badet_id == 0)){
					//edit to detail
					$this->db->where('adddet_id', $adddet_id);
					$this->db->update('t_adddet', $data_detail);
				}
					
			}
			
			return '1';
		}else{
			return '0';
		}
		
	}

	//function for update record
	function t_add_update($add_id, $add_kontrak, $add_type, $add_seq, $add_req_date, $add_req_no, $add_acc_date, $add_acc_no, $add_date, $add_no, 
				$add_begin_date, $add_end_date, $add_value,
				$array_adddet_id, $array_badet_id, $array_jobdet_id, $array_volume, $array_unit_id, $array_adddet_price){
		
		$data = array(
				"add_type"=>$add_type,
				"add_seq"=>$add_seq,
				"add_reqdate"=>$add_req_date,
				"add_reqno"=>$add_req_no,
				"add_accdate"=>$add_acc_date,
				"add_accno"=>$add_acc_no,
				"add_date"=>$add_date,
				"add_no"=>$add_no,
				"add_begin"=>$add_begin_date,
				"add_end"=>$add_end_date,
				"add_value"=>$add_value
				);
		$this->db->where('add_id', $add_id);
		$this->db->update('t_add', $data);
		
		for($i = 0; $i < sizeof($array_jobdet_id); $i++){
			$adddet_id = $array_adddet_id[$i];
			$badet_id = $array_badet_id[$i];
			$jobdet_id = $array_jobdet_id[$i];
			$volume = $array_volume[$i];
			$unit_id = $array_unit_id[$i];
			$adddet_price = $array_adddet_price[$i];
			
			$data_detail = array(
					"t_add_id"=>$add_id,
					"m_jobdet_id"=>$jobdet_id,
					"adddet_vol"=>$volume,
					"adddet_unit"=>$unit_id,
					"adddet_price"=>$adddet_price
					);
			if(($adddet_id == 0) && ($badet_id == 0)){
				//insert to detail
				$this->db->insert('t_adddet', $data_detail);
			}elseif (($adddet_id > 0) && ($badet_id == 0)){
				//edit to detail
				$this->db->where('adddet_id', $adddet_id);
				$this->db->update('t_adddet', $data_detail);
			}
				
		}
		
		return '1';
	}

	//fcuntion for delete record
	function t_add_delete($pkid){
		// You could do some checkups here and return '0' or other error consts.
		// Make a single query to delete all of the t_adds at the same time :
		if(sizeof($pkid)<1){
			return '0';
		} else if (sizeof($pkid) == 1){
			$this->db->delete('t_adddet', array('t_add_id' => $pkid[0]));
			$this->db->delete('t_add', array('add_id' => $pkid[0]));
		} else {
			for($i = 0; $i < sizeof($pkid); $i++){
				$this->db->delete('t_adddet', array('t_add_id' => $pkid[$i]));
				$this->db->delete('t_add', array('add_id' => $pkid[$i]));
			}
		}
		if($this->db->affected_rows()>0)
			return '1';
		else
			return '0';
	}

	//function for advanced search record
	function t_add_search($add_id,
			 $add_flag,
			 $add_code,
			 $add_type,
			 $add_name,
			 $add_shortname,
			 $add_pm,
			 $add_semcm,
			 $add_value,
			 $add_start,
			 $add_p1,
			 $add_p2,
			 $add_created_date,
			 $add_created_by,
			 $add_updated_date,
			 $add_updated_by,
			 $add_updated_count,
			 $m_branch,$start,$end){
		//full query
		$sql = "SELECT add_id,
					   add_flag,
					   add_code,
					   add_type,
					   add_name,
					   add_shortname,
					   add_pm,
					   add_semcm,
					   add_value,
					   add_start,
					   add_p1,
					   add_p2,
					   add_created_date,
					   add_created_by,
					   add_updated_date,
					   add_updated_by,
					   add_updated_count,
					   m_branch_id
				FROM   t_add";

		if($add_id!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_id) LIKE '%".addslashes(strtolower($add_id))."%'";
		};
		if($add_flag!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_flag) LIKE '%".addslashes(strtolower($add_flag))."%'";
		};
		if($add_code!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_code) LIKE '%".addslashes(strtolower($add_code))."%'";
		};
		if($add_type!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_type) LIKE '%".addslashes(strtolower($add_type))."%'";
		};
		if($add_name!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_name) LIKE '%".addslashes(strtolower($add_name))."%'";
		};
		if($add_shortname!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_shortname) LIKE '%".addslashes(strtolower($add_shortname))."%'";
		};
		if($add_pm!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_pm) LIKE '%".addslashes(strtolower($add_pm))."%'";
		};
		if($add_semcm!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_semcm) LIKE '%".addslashes(strtolower($add_semcm))."%'";
		};
		if($add_value!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_value) LIKE '%".addslashes(strtolower($add_value))."%'";
		};
		if($add_start!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_start) LIKE '%".addslashes(strtolower($add_start))."%'";
		};
		if($add_p1!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_p1) LIKE '%".addslashes(strtolower($add_p1))."%'";
		};
		if($add_p2!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_p2) LIKE '%".addslashes(strtolower($add_p2))."%'";
		};
		if($add_created_date!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_created_date) LIKE '%".addslashes(strtolower($add_created_date))."%'";
		};
		if($add_created_by!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_created_by) LIKE '%".addslashes(strtolower($add_created_by))."%'";
		};
		if($add_updated_date!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_updated_date) LIKE '%".addslashes(strtolower($add_updated_date))."%'";
		};
		if($add_updated_by!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_updated_by) LIKE '%".addslashes(strtolower($add_updated_by))."%'";
		};
		if($add_updated_count!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(add_updated_count) LIKE '%".addslashes(strtolower($add_updated_count))."%'";
		};
		if($m_branch!=''){
			$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
			$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
		};

		$result = $this->db->query($sql);
		$nbrows = $result->num_rows();
		$limit = $sql." LIMIT ".$start.",".$end;
		$result = $this->db->query($limit);

		if($nbrows>0){
			foreach($result->result() as $row){
				$arr[] = $row;
			}
			$jsonresult = json_encode($arr);
			return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
		} else {
			return '({"total":"0", "results":""})';
		}
	}

	//function for print record
	function t_add_print($add_id,
			 $add_flag,
			 $add_code,
			 $add_type,
			 $add_name,
			 $add_shortname,
			 $add_pm,
			 $add_semcm,
			 $add_value,
			 $add_start,
			 $add_p1,
			 $add_p2,
			 $add_created_date,
			 $add_created_by,
			 $add_updated_date,
			 $add_updated_by,
			 $add_updated_count,
			 $m_branch,$option,$filter){
		//full query
		$sql = "SELECT add_id,
					   add_flag,
					   add_code,
					   add_type,
					   add_name,
					   add_shortname,
					   add_pm,
					   add_semcm,
					   add_value,
					   add_start,
					   add_p1,
					   add_p2,
					   add_created_date,
					   add_created_by,
					   add_updated_date,
					   add_updated_by,
					   add_updated_count,
					   m_branch_id
			    FROM   t_add";

		if($option=='LIST'){
			$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
			$sql .= " (lower(add_id) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_flag) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_code) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_type) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_name) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_shortname) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_pm) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_semcm) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_value) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_start) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_p1) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_p2) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_created_date) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_created_by) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_updated_date) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_updated_by) LIKE '%".addslashes(strtolower($filter))."%' OR lower(add_updated_count) LIKE '%".addslashes(strtolower($filter))."%' OR lower(m_branch_id) LIKE '%".addslashes(strtolower($filter))."%' )";
		} else if($option=='SEARCH'){
			if($add_id!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_id) LIKE '%".addslashes(strtolower($add_id))."%'";
			};
			if($add_flag!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_flag) LIKE '%".addslashes(strtolower($add_flag))."%'";
			};
			if($add_code!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_code) LIKE '%".addslashes(strtolower($add_code))."%'";
			};
			if($add_type!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_type) LIKE '%".addslashes(strtolower($add_type))."%'";
			};
			if($add_name!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_name) LIKE '%".addslashes(strtolower($add_name))."%'";
			};
			if($add_shortname!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_shortname) LIKE '%".addslashes(strtolower($add_shortname))."%'";
			};
			if($add_pm!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_pm) LIKE '%".addslashes(strtolower($add_pm))."%'";
			};
			if($add_semcm!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_semcm) LIKE '%".addslashes(strtolower($add_semcm))."%'";
			};
			if($add_value!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_value) LIKE '%".addslashes(strtolower($add_value))."%'";
			};
			if($add_start!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_start) LIKE '%".addslashes(strtolower($add_start))."%'";
			};
			if($add_p1!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_p1) LIKE '%".addslashes(strtolower($add_p1))."%'";
			};
			if($add_p2!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_p2) LIKE '%".addslashes(strtolower($add_p2))."%'";
			};
			if($add_created_date!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_created_date) LIKE '%".addslashes(strtolower($add_created_date))."%'";
			};
			if($add_created_by!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_created_by) LIKE '%".addslashes(strtolower($add_created_by))."%'";
			};
			if($add_updated_date!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_updated_date) LIKE '%".addslashes(strtolower($add_updated_date))."%'";
			};
			if($add_updated_by!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_updated_by) LIKE '%".addslashes(strtolower($add_updated_by))."%'";
			};
			if($add_updated_count!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_updated_count) LIKE '%".addslashes(strtolower($add_updated_count))."%'";
			};
			if($m_branch!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
			};
		}
		$query = $this->db->query($sql);
		return $query->result();
	}

	//function  for export to excel
	function t_add_export_excel($add_id ,$add_flag ,$add_code ,$add_type ,$add_name ,$add_shortname ,$add_pm ,$add_semcm ,$add_value ,$add_start ,$add_p1 ,$add_p2 ,$add_created_date ,$add_created_by ,$add_updated_date ,$add_updated_by ,$add_updated_count ,$m_branch ,$option,$filter){
		//full query
		$sql = "SELECT add_id,
					   add_flag,
					   add_code,
					   add_type,
					   add_name,
					   add_shortname,
					   add_pm,
					   add_semcm,
					   add_value,
					   add_start,
					   add_p1,
					   add_p2,
					   add_created_date,
					   add_created_by,
					   add_updated_date,
					   add_updated_by,
					   add_updated_count,
					   m_branch_id				    FROM   t_add";

		if($option=='LIST'){
			$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
			$sql .= "(lower(add_id) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_flag) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_code) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_type) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_name) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_shortname) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_pm) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_semcm) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_value) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_start) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_p1) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_p2) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_created_date) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_created_by) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_updated_date) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_updated_by) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(add_updated_count) LIKE '%".addslashes(strtolower($filter))."%' OR
					 lower(m_branch_id) LIKE '%".addslashes(strtolower($filter))."%' )";
		} else if($option=='SEARCH'){
			if($add_id!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_id) LIKE '%".addslashes(strtolower($add_id))."%'";
			};
			if($add_flag!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_flag) LIKE '%".addslashes(strtolower($add_flag))."%'";
			};
			if($add_code!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_code) LIKE '%".addslashes(strtolower($add_code))."%'";
			};
			if($add_type!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_type) LIKE '%".addslashes(strtolower($add_type))."%'";
			};
			if($add_name!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_name) LIKE '%".addslashes(strtolower($add_name))."%'";
			};
			if($add_shortname!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_shortname) LIKE '%".addslashes(strtolower($add_shortname))."%'";
			};
			if($add_pm!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_pm) LIKE '%".addslashes(strtolower($add_pm))."%'";
			};
			if($add_semcm!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_semcm) LIKE '%".addslashes(strtolower($add_semcm))."%'";
			};
			if($add_value!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_value) LIKE '%".addslashes(strtolower($add_value))."%'";
			};
			if($add_start!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_start) LIKE '%".addslashes(strtolower($add_start))."%'";
			};
			if($add_p1!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_p1) LIKE '%".addslashes(strtolower($add_p1))."%'";
			};
			if($add_p2!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_p2) LIKE '%".addslashes(strtolower($add_p2))."%'";
			};
			if($add_created_date!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_created_date) LIKE '%".addslashes(strtolower($add_created_date))."%'";
			};
			if($add_created_by!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_created_by) LIKE '%".addslashes(strtolower($add_created_by))."%'";
			};
			if($add_updated_date!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_updated_date) LIKE '%".addslashes(strtolower($add_updated_date))."%'";
			};
			if($add_updated_by!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_updated_by) LIKE '%".addslashes(strtolower($add_updated_by))."%'";
			};
			if($add_updated_count!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(add_updated_count) LIKE '%".addslashes(strtolower($add_updated_count))."%'";
			};
			if($m_branch!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
			};
		}
		$query = $this->db->query($sql);
		return $sql;
	}

}
?>